package org.club.mapper;

import org.apache.ibatis.annotations.*;
import org.club.common.model.entity.ClubInfo;
import org.club.common.model.entity.UserInfo;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface AdminInfoMapper {
    //查询用户信息
    List<UserInfo> listUserClubInfo(@Param("realName") String realName, @Param("idCard") String idCard);
    //查询社团信息
    List<ClubInfo> listClubInfo(@Param("name") String name,@Param("status") Integer status);
    //查询该用户是否为社团管理员
    String setClubsAdmin(@Param("userID") Long userId);
    //删除用户信息
    Integer deleteUser(@Param("userID") Long userId,@Param("username") String username);
    //修改用户状态
    Integer setUserstatus(@Param("userId") Long userId,@Param("status") String status);
    //修改社团活动状态
    Integer setClubactivitystatus(@Param("clubId") Long clubId,@Param("activityStatus")  String activityStatus);
    //查询符合条件的用户总数
    Integer seletCount(@Param("idCard") String idCard,@Param("realName") String realName);
    //查询符合条件的社团总数
    Integer seletClubCount(@Param("status") Integer status,@Param("name") String name);
//    修改社团审核状态
    Integer setClubstatus(@Param("clubId") Long clubId,@Param("status") String status);
//    查询社团管理员
    @Select("SELECT user_id FROM club_admins WHERE club_id = #{clubId}")
    Long selectUserId(@Param("clubId") Long clubId);
////查询user_id是否存在user_roles表，如果不存在则插入，存在则删除后插入
    @Select("SELECT COUNT(*) FROM user_roles WHERE user_id = #{userId}")
    Integer selectUser_roles(@Param("userId") Long userId);
//更新user_roles表
    @Update("UPDATE user_roles SET role_id= 2 WHERE user_id = #{userId}")
    void UpdeateFileFromOss(@Param("userId")Long userId);
//插入user_roles表
    @Insert("INSERT INTO user_roles (user_id, role_id) VALUES (#{userId}, 2)")
    Integer Insertionuser_roles(@Param("userId") Long userId);


    //社团活跃度
    // 在AdminInfoMapper.java中添加
    @Select("SELECT c.id as clubId, c.name as clubName, " +
            "COUNT(DISTINCT a.id) as eventCount, " +
            "COUNT(DISTINCT cm.user_id) as memberCount, " +
            "MAX(a.created_at) as lastActivityTime " +
            "FROM clubs c " +
            "LEFT JOIN activities a ON c.id = a.club_id " +
            "LEFT JOIN club_members cm ON c.id = cm.club_id " +
            "WHERE c.status = 1 " +
            "AND (#{startDate} IS NULL OR a.created_at >= #{startDate}) " +
            "AND (#{endDate} IS NULL OR a.created_at <= #{endDate}) " +
            "GROUP BY c.id, c.name")
    List<Map<String, Object>> getClubActivityData(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

    @Select("SELECT DATE(created_at) as date, COUNT(*) as count " +
            "FROM activities " +
            "WHERE (#{startDate} IS NULL OR created_at >= #{startDate}) " +
            "AND (#{endDate} IS NULL OR created_at <= #{endDate}) " +
            "GROUP BY DATE(created_at) " +
            "ORDER BY date")
    List<Map<String, Object>> getActivityTrendData(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

}
